package demo;

import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.junit.jupiter.api.Test;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @Author: zjq
 * @Date: create in 2022/7/12 17:09
 */
public class testExcel {
    //poi的锁测试

    //这个是锁住了整个sheet,默认锁住所有的
    @Test
    public void contextLoads() throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet名称");

        XSSFCellStyle lockstyle = wb.createCellStyle();
        lockstyle.setLocked(true);//设置锁定
        lockstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());//设置上锁的单元格背景色

        XSSFCellStyle unlockStyle = wb.createCellStyle();
        unlockStyle.setLocked(false);//设置未锁定


        for (int i = 0; i < 10; i++) {
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                XSSFCell cell = row.createCell(j);
                cell.setCellStyle(unlockStyle);//默认是锁定状态；将所有单元格设置为：未锁定；然后再对需要上锁的单元格单独锁定
                if (j == 1) {//这里可以根据需要进行判断;我这就将第2列上锁了
                    cell.setCellStyle(lockstyle);//将需要上锁的单元格进行锁定
                    cell.setCellValue("上锁了");
                } else {
                    cell.setCellValue("没上锁了");
                }
            }
        }
        //sheet添加保护，这个一定要否则光锁定还是可以编辑的
        sheet.protectSheet("123456");
        FileOutputStream os = new FileOutputStream("I:\\java\\workbook.xlsx");
        wb.write(os);
        os.close();
    }

    //放通某些列，默认上锁所有，把不需要的放通就好，ExcelUtil.createClassTemplateWithData
    @Test
    public void contextLoads2() throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet名称");

        XSSFCellStyle lockstyle = wb.createCellStyle();
        lockstyle.setLocked(true);//设置锁定
        lockstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());//设置上锁的单元格背景色

        XSSFCellStyle unlockStyle = wb.createCellStyle();
        unlockStyle.setLocked(false);//设置未锁定


        for (int i = 0; i < 5; i++) {
            XSSFRow row = sheet.createRow(i);
            sheet.setColumnWidth(i, 4000);
            sheet.setDefaultColumnStyle(i, unlockStyle);//操纵的是列

            for (int j = 0; j < 5; j++) {
                XSSFCell cell = row.createCell(j);
                cell.setCellStyle(unlockStyle);//默认是锁定状态；将所有单元格设置为：未锁定；然后再对需要上锁的单元格单独锁定
                if (j == 1) {//这里可以根据需要进行判断;我这就将第2列上锁了
                    cell.setCellStyle(lockstyle);//将需要上锁的单元格进行锁定
                    cell.setCellValue("上锁了");
                } else {
                    cell.setCellValue("没上锁了");
                }
            }
        }

        // 设置锁定的单元格为写保护 注释2
        //sheet表加密：等效excel的审阅菜单下的保护工作表
        sheet.protectSheet(new String("333"));//333是密码
        sheet.enableLocking();
        CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
        sheetProtection.setSelectLockedCells(false);
        sheetProtection.setSelectUnlockedCells(false);
        sheetProtection.setFormatCells(true);
        sheetProtection.setFormatColumns(true);
        sheetProtection.setFormatRows(true);
        sheetProtection.setInsertColumns(true);
        sheetProtection.setInsertRows(false);
        sheetProtection.setInsertHyperlinks(true);
        sheetProtection.setDeleteColumns(true);
        sheetProtection.setDeleteRows(true);
        sheetProtection.setSort(false);
        sheetProtection.setAutoFilter(false);
        sheetProtection.setPivotTables(true);
        sheetProtection.setObjects(true);
        sheetProtection.setScenarios(true);
        FileOutputStream os = new FileOutputStream("I:\\java\\workbook2.xlsx");
        wb.write(os);
        os.close();
    }

    //最初始的代码
    @Test
    public void a() {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet();
            //设置页名称
            workBook.setSheetName(0, "sheet名");
            //默认宽度
            sheet.setDefaultColumnWidth(15);

            //设置title样式
            XSSFCellStyle titleStyle = workBook.createCellStyle();
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());

            //单元格锁定的样式
            XSSFCellStyle lockstyle = workBook.createCellStyle();
            lockstyle.setLocked(true);
            lockstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            //单元格不锁定的样式
            XSSFCellStyle unlockstyle = workBook.createCellStyle();
            unlockstyle.setLocked(false);

            //设置导出表头
            String[] header = new String[]{"ID(不可编辑)", "字段1", "字段2", "字段3",
                    "字段4", "字段5", "字段6", "字段7",
                    "字段8", "字段9", "字段10", "字段11", "字段12", "字段13"};
            //创建表头行
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell;
            //插入Excel表头
            for (short i = 0; i < header.length; i++) {
                cell = row.createCell(i);
                if (i == 0) {
                    cell.setCellStyle(lockstyle);
                } else if (i == 6 || i == 7 || i == 8 || i == 10 || i == 11 || i == 12) {
                    //设置列格式，注释1
                    sheet.setColumnWidth(i, 4000);
                    sheet.setDefaultColumnStyle(i, unlockstyle);
                } else {
                    cell.setCellStyle(titleStyle);
                    //设置列格式，注释1
                    sheet.setColumnWidth(i, 4000);
                    sheet.setDefaultColumnStyle(i, unlockstyle);
                }
                XSSFRichTextString text = new XSSFRichTextString(header[i]);
                cell.setCellValue(text);

            }

            //下拉列表
            CellRangeAddressList addressList1 = null;
            XSSFDataValidation dataValidation1 = null;

            CellRangeAddressList addressList2 = null;
            XSSFDataValidation dataValidation2 = null;

            //运输方式
            String[] textList1 = new String[]{"值1", "值二", "值三"};
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(textList1);
            //操作类型
            String[] textList2 = {"I", "U", "D"};
            XSSFDataValidationConstraint dvConstraint2 = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(textList2);

            //运输方式下拉 首行，末行，首列，末列
            addressList1 = new CellRangeAddressList(1, 65536, 4, 4);
            dataValidation1 = (XSSFDataValidation) dvHelper.createValidation(
                    dvConstraint1, addressList1);
            // 07默认setSuppressDropDownArrow(true);
            // validation.setSuppressDropDownArrow(true);
            // validation.setShowErrorBox(true);
            sheet.addValidationData(dataValidation1);

            //操作类型下拉 首行，末行，首列，末列
            addressList2 = new CellRangeAddressList(1, 65536, 13, 13);
            dataValidation2 = (XSSFDataValidation) dvHelper.createValidation(
                    dvConstraint2, addressList2);
            // 07默认setSuppressDropDownArrow(true);
            // validation.setSuppressDropDownArrow(true);
            // validation.setShowErrorBox(true);
            sheet.addValidationData(dataValidation2);

            //遍历插入数据
            for (int i = 0; i < 10; i++) {
                row = sheet.createRow(i + 1);
                //
                cell = row.createCell(0);
                cell.setCellValue(i);
                cell.setCellStyle(lockstyle);

                //
                cell = row.createCell(1);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //          cell = row.createCell(2);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(3);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);

                //
                cell = row.createCell(4);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(5);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(6);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(7);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(8);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(9);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(10);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(11);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);
                //
                cell = row.createCell(12);
                cell.setCellValue(i);
                cell.setCellStyle(unlockstyle);

                //
                cell = row.createCell(13);
                cell.setCellValue("U");
                cell.setCellStyle(unlockstyle);
            }


            // 设置锁定的单元格为写保护 注释2
            //sheet表加密：等效excel的审阅菜单下的保护工作表
            //sheet.protectSheet(new String("333"));//333是密码
            sheet.enableLocking();
            CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
            sheetProtection.setSelectLockedCells(false);
            sheetProtection.setSelectUnlockedCells(false);
            sheetProtection.setFormatCells(true);
            sheetProtection.setFormatColumns(true);
            sheetProtection.setFormatRows(true);
            sheetProtection.setInsertColumns(true);
            sheetProtection.setInsertRows(false);
            sheetProtection.setInsertHyperlinks(true);
            sheetProtection.setDeleteColumns(true);
            sheetProtection.setDeleteRows(true);
            sheetProtection.setSort(false);
            sheetProtection.setAutoFilter(false);
            sheetProtection.setPivotTables(true);
            sheetProtection.setObjects(true);
            sheetProtection.setScenarios(true);

            FileOutputStream os = new FileOutputStream("I:\\java\\workbook11111.xlsx");
            workBook.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
